import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellRangeAddress8Bit;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.eventusermodel.XSSFReader;

import java.io.*;

/**
 * Created by mengwei666 on 2016/10/20.
 */
public class Main2 {
    public static void main(String[] args) throws IOException{
        InputStream in = new BufferedInputStream(new FileInputStream("src/text.xls"));
        HSSFWorkbook book=new HSSFWorkbook(in);         //获取Excel对象
        HSSFSheet sheet=book.getSheetAt(0);             //获取工作表
        HSSFRow row=sheet.getRow(1);                    //获取行
        System.out.println("读取第一条信息:");
        System.out.println(row.getCell(0));             //读单元格
        System.out.println(row.getCell(1));
        FileOutputStream out =new FileOutputStream("src/newSheet.xls");

        HSSFCellStyle style=book.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));    //设置第1,2列单元格格式
        style.setAlignment(HorizontalAlignment.CENTER);                  //设置第1,2列单元格水平对齐
        for(int i=0;i<6;i++){
            row=sheet.getRow(i);
            for(int j=0;j<2;j++) {
                row.getCell(j).setCellStyle(style);
            }
        }

        //合并单元格
        HSSFRow aRow=sheet.getRow(0);
        HSSFCell mergedCell=aRow.createCell(3);
        mergedCell.setCellValue("合并");
        CellRangeAddress region=new CellRangeAddress(0,5,3,3);
        sheet.addMergedRegion(region);

        //为单元格设置公式
        row=sheet.createRow(6);
        HSSFCell cell=row.createCell(0);
        cell.setCellValue("总时长");
        cell=row.createCell(1);
        cell.setCellFormula("B2+B3+B4+B5+B6");

        book.write(out);
        in.close();
        out.close();
    }
}
